package com.farseer810.dao;

import java.util.*;
import java.sql.*;

import com.farseer810.model.Class;
import com.farseer810.model.Student;
import com.farseer810.service.StudentService;
import com.farseer810.utils.DatabaseHelper;

public class StudentDao {
	private StudentDao() {
	}

	public static void addStudent(Student student) throws Exception {
		Connection connection = DatabaseHelper.getConnection();
		PreparedStatement statement = connection
				.prepareStatement("INSERT INTO student(number, name, class_id, chinese, math, english, total_score)"
						+ " values(?, ?, ?, ?, ?, ?, ?)");
		statement.setString(1, student.getNumber());
		statement.setString(2, student.getName());
		statement.setInt(3, student.getBelongClass().getId());
		statement.setInt(4, student.getChineseScore());
		statement.setInt(5, student.getMathScore());
		statement.setInt(6, student.getEnglishScore());
		statement.setInt(7, student.getChineseScore() + student.getMathScore() + student.getEnglishScore());

		statement.executeUpdate();

		statement.close();
		connection.close();
	}

	public static void modifyStudent(Student student) throws Exception {
	}

	public static void deleteStudent(Student student) throws Exception {
	}

	public static boolean isStudentNumberExist(String number) throws Exception {
		boolean isExist = true;
		Connection connection = DatabaseHelper.getConnection();
		PreparedStatement statement = connection.prepareStatement("SELECT id FROM student WHERE number=?");
		statement.setString(1, number);
		ResultSet resultSet = statement.executeQuery();

		resultSet.last();
		if (resultSet.getRow() == 0)
			isExist = false;

		statement.close();
		connection.close();

		return isExist;
	}

	public static List<Student> getStudentGradeList(int sortType) throws Exception {
		Connection connection = DatabaseHelper.getConnection();
		Statement statement = connection.createStatement();
		String sql = "SELECT s.id as id, number, s.name as name, class_id, c.name as class_name, chinese, math, english "
				+ "FROM student as s, class as c where class_id=c.id ";

		switch (sortType) {
		case StudentService.SORT_TOTAL_SCORE:
			sql += " ORDER BY total_score DESC";
			break;
		case StudentService.SORT_CHINESE:
			sql += " ORDER BY chinese DESC";
			break;
		case StudentService.SORT_MATH:
			sql += " ORDER BY math DESC";
			break;
		case StudentService.SORT_ENGLISH:
			sql += " ORDER BY english DESC";
			break;

		default:
			break;
		}

		ResultSet resultSet = statement.executeQuery(sql);
		List<Student> result = new ArrayList<Student>();
		while (resultSet.next()) {
			Class belongClass = new Class(resultSet.getInt("class_id"), resultSet.getString("class_name"));
			Student student = new Student(resultSet.getInt("id"), resultSet.getString("number"),
					resultSet.getString("name"), belongClass, resultSet.getInt("chinese"), resultSet.getInt("math"),
					resultSet.getInt("english"));
			result.add(student);
		}

		statement.close();
		connection.close();
		return result;
	}

	public static void createTable() throws Exception {
		Connection connection = DatabaseHelper.getConnection();
		Statement statement = connection.createStatement();

		ResultSet resultSet = statement.executeQuery("SHOW TABLES LIKE 'student'");

		if (!resultSet.next()) {
			String sql = "CREATE TABLE student (" + "id int auto_increment primary key," + "number varchar(30),"
					+ "name varchar(30)," + "class_id int," + "chinese int," + "math int," + "english int,"
					+ "total_score int," + "foreign key(class_id) references class(id)"
					+ ")ENGINE=InnoDB DEFAULT CHARSET=utf8";
			statement.executeUpdate(sql);
		}

		statement.close();
		connection.close();

	}
}
